package com.imedway.ihe.dtz.xds.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.openehealth.ipf.commons.ihe.xds.core.metadata.AssociationType;

import com.imedway.ihe.dtz.xds.dto.XdsDto;
import com.imedway.ihe.dtz.xds.entity.IheXdsFileInfo;

import cn.hutool.json.JSONUtil;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MysqlDbUtils {

	static String url = "jdbc:mysql://113.140.81.69:13306/xds";
	//	static String url = "jdbc:mysql://192.168.5.61:3306/xds";
	static String driver = "com.mysql.cj.jdbc.Driver";
	static String user = "xds";
	static String password = "xds";
	
	public static Connection getConn() {
		Connection conn = null;
        try {
            // 加载MySQL JDBC驱动
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
		return conn;
		
	}
	
	public static void insertDb(XdsDto dto) throws SQLException {
		Connection conn = getConn();
		List<IheXdsFileInfo> iheXdsInfoList = dto.getIheXdsInfoFileList();
		log.info(JSONUtil.toJsonStr(iheXdsInfoList));
		String sql = "insert into t_ihe_xds_file_info ("
				+ "id,create_time,update_time,status,file_path,"
				+ "patient_id,unique_id,source_id,content_type,file_name,"
				+ "mime_type"
				+ ") "
				+ "value"
				+ "("
				+ "?,?,?,?,?,"
				+ "?,?,?,?,?,"
				+ "?"
				+ ")";
		PreparedStatement stmt = conn.prepareStatement(sql);
		for(IheXdsFileInfo iheXdsInfo : iheXdsInfoList) {
			stmt.setString(1, iheXdsInfo.getId());
			stmt.setTimestamp(2, iheXdsInfo.getCreateTime());
			stmt.setTimestamp(3, iheXdsInfo.getUpdateTime());
			stmt.setString(4, iheXdsInfo.getStatus());
			stmt.setString(5, iheXdsInfo.getFilePath());
			stmt.setString(6, iheXdsInfo.getPatientId());
			stmt.setString(7, iheXdsInfo.getUniqueId());
			stmt.setString(8, iheXdsInfo.getSourceId());
			stmt.setString(9, iheXdsInfo.getContentType());
			stmt.setString(10, iheXdsInfo.getFileName());
			stmt.setString(11, iheXdsInfo.getMimeType());
			stmt.addBatch();
		}			
		int[] batchResults = stmt.executeBatch();
		for(int i:batchResults) {
			log.info(String.valueOf(i));
		}
		if(stmt!=null) {
			stmt.close();
		}
		if(conn!=null) {
			conn.close();
		}
	
	}
	
	public static void updateDb(XdsDto dto) throws SQLException {
		Connection conn = getConn();
		List<IheXdsFileInfo> iheXdsInfoList = dto.getIheXdsInfoFileList();	
		AssociationType associationType = dto.getAssociationType();
		PreparedStatement stmt = null;
		String sql =null;
		for(IheXdsFileInfo iheXdsInfo : iheXdsInfoList) {
			switch (associationType) {
			case REPLACE:
				sql = "update t_ihe_xds_file_info set update_time=? where unique_id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setTimestamp(1, iheXdsInfo.getCreateTime());
				stmt.setString(2, iheXdsInfo.getUniqueId());
				stmt.addBatch();
				break;
			case APPEND:
				sql = "update t_ihe_xds_file_info set update_time=? where unique_id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setTimestamp(1, iheXdsInfo.getCreateTime());
				stmt.setString(2, iheXdsInfo.getUniqueId());
				stmt.addBatch();

				break;
			case TRANSFORM:
				sql = "update t_ihe_xds_file_info set update_time=? and file_path=? and  mime_type=? where unique_id=?";
				stmt = conn.prepareStatement(sql);
				stmt.setTimestamp(1, iheXdsInfo.getCreateTime());
				stmt.setString(2, iheXdsInfo.getFileName());
				stmt.setString(3, iheXdsInfo.getMimeType());
				stmt.setString(4, iheXdsInfo.getUniqueId());
				stmt.addBatch();

				break;
			default:
				break;
			}			
		}
		int[] batchResults = stmt.executeBatch();
		for(int i:batchResults) {
			log.info(String.valueOf(i));
		}
		if(stmt!=null) {
			stmt.close();
		}
		if(conn!=null) {
			conn.close();
		
		}
	}
	public static List<IheXdsFileInfo> selectDb(XdsDto dto) throws SQLException {
		Connection conn = getConn();
		List<IheXdsFileInfo> iheXdsInfoList = dto.getIheXdsInfoFileList();
		List<IheXdsFileInfo> newIheXdsInfoList = dto.getIheXdsInfoFileList();

		log.info(JSONUtil.toJsonStr(iheXdsInfoList));
		String sql = "select "
				+ "id,create_time,update_time,status,file_path,"
				+ "patient_id,unique_id,source_id,content_type,file_name,"
				+ "mime_type"
				+ " from t_ihe_xds_file_info "
				+ " where "
				+ " patient_id = ? or unique_id = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);	
        List<ResultSet> resultSets = new ArrayList<>();
		for(IheXdsFileInfo iheXdsInfo : iheXdsInfoList) {
			stmt.setString(1, iheXdsInfo.getPatientId());
			stmt.setString(2, iheXdsInfo.getUniqueId());
            ResultSet resultSet = stmt.executeQuery();
            resultSets.add(resultSet);
            
		}			
		// 处理结果集
        for (ResultSet resultSet : resultSets) {
            while (resultSet.next()) {
            	IheXdsFileInfo iheXdsFileInfo = new IheXdsFileInfo();
            	iheXdsFileInfo.setId(resultSet.getString("id"));
            	iheXdsFileInfo.setCreateTime(resultSet.getTimestamp("create_time"));
            	iheXdsFileInfo.setUpdateTime(resultSet.getTimestamp("update_time"));
            	iheXdsFileInfo.setStatus(resultSet.getString("status"));
            	iheXdsFileInfo.setFilePath(resultSet.getString("file_path"));
            	iheXdsFileInfo.setPatientId(resultSet.getString("patient_id"));
            	iheXdsFileInfo.setUniqueId(resultSet.getString("unique_id"));
            	iheXdsFileInfo.setSourceId(resultSet.getString("source_id"));
            	iheXdsFileInfo.setContentType(resultSet.getString("content_type"));
            	iheXdsFileInfo.setFileName(resultSet.getString("file_name"));
            	iheXdsFileInfo.setMimeType(resultSet.getString("mime_type"));
            	newIheXdsInfoList.add(iheXdsFileInfo);
                // 获取并处理结果
                // ...
            }
            resultSet.close(); // 关闭结果集
        }
        log.info(JSONUtil.toJsonStr(newIheXdsInfoList));
		if(stmt!=null) {
			stmt.close();
		}
		if(conn!=null) {
			conn.close();
		}
		return newIheXdsInfoList;
	
	}
	
}
